#!/bin/sh

#
# stat hotel(iffrom = 'qunarHotel') queryword(query detail booking)
# stat bnb queryword(query)
#
# author:taoran.xu
# date:2012-04-19

QDATE=`/bin/date -d 'last day' +%F`
QMONTH=`/bin/date -d 'last day' +%Y%m`

if [ $# -eq 1 ]; then
	QDATE="$1"
	QMONTH=`/bin/date -d "$1" +%Y%m`
fi

RESULTFILE=/logs/api/hotel/hs/hotellog_for_hs_$QDATE.txt


echo "#type cityname query querycnt detailcnt bookcnt---" > $RESULTFILE

#hotel
SQL="select 'hotel' as ttype, a.cityname,a.queryWord,a.query,b.detail,c.book from (select cityname, queryWord,count(*) query from hotelsearchinfo$QMONTH where queryWord<>'' and iffrom like 'qunarHotel%' and reportDate = '$QDATE' group by cityname,queryWord ) a left join (select replace(hotelseq,concat('_',SUBSTRING_INDEX(hotelseq,'_',-1)),'') city_name,queryWord,count(*) detail from hoteldetailclickinfo$QMONTH where queryWord<>'' and iffrom like 'qunarHotel%' and reportDate = '$QDATE' group by city_name,queryWord ) b on concat(a.cityname,a.queryWord)=concat(b.city_name,b.queryWord) left join (select replace(hotelseq,concat('_',SUBSTRING_INDEX(hotelseq,'_',-1)),'') city_name,queryWord,count(*) book from hotelbookinginfo$QMONTH where queryWord<>'' and iFfilter='0' and reportDate='$QDATE' and iffrom like 'qunarHotel%' group by city_name,queryWord) c on concat(a.cityname,a.queryWord)=concat(c.city_name,c.queryWord)  order by a.query desc, b.detail desc,c.book desc "
/usr/bin/mysql -u qunarlog -p2HhxtsSel6rg -h 192.168.0.253 logstat --default-character-set=utf8  -N -e  "$SQL" >> $RESULTFILE
#echo $SQL


#bnb
SQL="select 'bnb' as ttype,cityname,queryword,sum(num) from bnb_query where statdate = '$QDATE' group by cityname,queryword order by num desc "
/usr/bin/mysql -u qunarlog -p2HhxtsSel6rg -h 192.168.0.253 logstat --default-character-set=utf8  -N -e  "$SQL" >> $RESULTFILE
#echo $SQL


